﻿#include "datasqlshow.h"
#include "ui_datasqlshow.h"
#include <libssh/libssh.h>
DataSqlshow::DataSqlshow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::DataSqlshow)
{
    ui->setupUi(this);
    QStatusBar* zt = this->statusBar();
    QLabel* copyRight = new QLabel(QString::fromLocal8Bit("copyright:ksen"), this);
    zt->addWidget(copyRight);
    ui->tableWidget->verticalHeader()->setHidden(true);

    //opensql
    dbshow = QSqlDatabase::addDatabase("QSQLITE");
    dbshow.setDatabaseName(QCoreApplication::applicationDirPath() + "/" + "pkuncii2.db");
    //qDebug() << dbPath;
    if (!dbshow.open()) {

        qDebug() << QString::fromLocal8Bit("打开数据库失败") << dbshow.lastError().text();
        return;
    }

    datashow();
	QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
	db.setHostName("172.16.10.105");
	db.setDatabaseName("sqltest");
	db.setUserName("ksen");
	db.setPassword("773611784");

	if (!db.open()) {
		qDebug() << "数据库连接失败：" << db.lastError().text();

	}

	qDebug() << "数据库连接成功！";

	// 这里可以执行对 imac 表的操作，例如查询
	QSqlQuery query;
	query.exec("SELECT * FROM imac_sent");
	while (query.next()) {
		// 根据表的字段获取数据
		int id = query.value(0).toInt();
		QString someField = query.value(1).toString();
		// 处理数据
		qDebug() << "ID:" << id << " Field:" << someField;
	}

	db.close();
    //
}
//刷新表格
void DataSqlshow::on_pushButton_5_clicked() {


   ui->tableWidget->clearContents();
   ui->tableWidget->setRowCount(0); // 清空行数
   datashow();
}


//删除信息
void DataSqlshow::on_pushButton_3_clicked() {
    //int currentRow = ui->tableWidget->currentRow();
    //if (currentRow != -1) { // 确保有行被选中
    //    QString name = ui->tableWidget->item(currentRow, 0)->text();   // 获取该行的第一列内容
    //    QString group = ui->tableWidget->item(currentRow, 1)->text();  // 获取该行的第二列内容
    //    QString type = ui->tableWidget->item(currentRow, 2)->text();   // 获取该行的第三列内容
    //    QString yxip = ui->tableWidget->item(currentRow, 3)->text();   // 获取该行的第四列内容
    //    QString yxmac = ui->tableWidget->item(currentRow, 4)->text();  // 获取该行的第五列内容
    //    QString wifiip = ui->tableWidget->item(currentRow, 5)->text(); // 获取该行的第六列内容
    //    QString wifimac = ui->tableWidget->item(currentRow, 6)->text(); // 获取该行的第七列内容

    //    qDebug() << "Selected Row:" << currentRow;
    //    qDebug() << "Name:" << name;
    //    qDebug() << "Group:" << group;
    //    qDebug() << "Type:" << type;
    //    qDebug() << "YXIP:" << yxip;
    //    qDebug() << "YXMAC:" << yxmac;
    //    qDebug() << "WiFiIP:" << wifiip;
    //    qDebug() << "WiFiMAC:" << wifimac;
    //}
    //else {
    //    qDebug() << "No row selected.";
    //}
    int currentRow = ui->tableWidget->currentRow(); // 获取当前选择的行索引
    //弹出警告，确认是否删除。
    QMessageBox::StandardButton reply;

    
    reply = QMessageBox::question(nullptr,
        QString::fromLocal8Bit("信息"),
        QString::fromLocal8Bit("是否确认删除；"),
        QMessageBox::Yes | QMessageBox::No);
 
    if (reply == QMessageBox::Yes)
    {
            qDebug() << QString::fromLocal8Bit("执行删除");  
            if (currentRow != -1) { // 确保有行被选中
            QTableWidgetItem* item = ui->tableWidget->item(currentRow, 0); // 假设第一列是唯一标识符（如 id）

            if (item) {
                QString id = item->text(); // 获取唯一标识符
                QSqlQuery query;
            
                // 执行删除语句
                query.prepare("DELETE FROM imac WHERE id = :id");
                query.bindValue(":id", id);

                if (query.exec()) {
                    qDebug() << "数据删除成功!";
                    on_pushButton_5_clicked(); // 删除成功后刷新表格
                }
                else {
                    qDebug() << "数据删除失败:" << query.lastError().text();
                }
            }
            else {
                qDebug() << "没有找到唯一标识符。";
            }
        }
        else {
            qDebug() << "没有选择行。";
        }
    }
    else {
        return;
    }
  




}

//show data
void DataSqlshow::datashow() {
    
    QSqlQuery query("SELECT * FROM imac"); 

    if (!query.exec()) {
        qDebug() << QString::fromLocal8Bit("查询失败") << query.lastError().text();
       //return -1;
    }

    QSqlRecord record = query.record();
    ui->tableWidget->setColumnCount(record.count()-1);
    QStringList headers;
    for (int i = 1; i < record.count(); ++i) {
        headers << record.fieldName(i);
    }
    ui->tableWidget->setHorizontalHeaderLabels(headers);
    int row = 0;
    while (query.next()) {
        ui->tableWidget->insertRow(row);
        for (int col = 1; col < record.count(); ++col) {
            QTableWidgetItem* item = new QTableWidgetItem(query.value(col).toString());
            ui->tableWidget->setItem(row, col-1, item);
        }
        ++row;
    }
   // ui->tableWidget->horizontalHeader()->setSectionResizeMode(QHeaderView::ResizeToContents);
    ui->tableWidget->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ui->tableWidget->setEditTriggers(QAbstractItemView::NoEditTriggers);

}

//提交路由器
void DataSqlshow::on_pushButton_ssh_clicked() 
{
	ssh_session session;
	int rc;

	session = ssh_new();
	if (session == NULL)
		exit(-1);

	ssh_options_set(session, SSH_OPTIONS_HOST, "172.16.100.254");
	ssh_options_set(session, SSH_OPTIONS_USER, "admin");

	rc = ssh_connect(session);
	if (rc != SSH_OK) 
    {
		fprintf(stderr,
			"Error connecting to remote host: %s\n",
			ssh_get_error(session));
		ssh_free(session);
		exit(-1);
	}

	rc = ssh_userauth_password(session, NULL, "Best@12345");
	if (rc != SSH_AUTH_SUCCESS) 
    {
		fprintf(stderr, "Authentication failed: %s\n", ssh_get_error(session));
		ssh_disconnect(session);
		ssh_free(session);

	}

	ssh_channel channel = ssh_channel_new(session);
	if (channel == NULL) 
    {
		fprintf(stderr,
			"Error creating SSH channel: %s\n",
			ssh_get_error(session));
		ssh_disconnect(session);
		ssh_free(session);

	}

	rc = ssh_channel_open_session(channel);
	if (rc != SSH_OK) {
		fprintf(stderr,
			"Error opening SSH channel: %s\n",
			ssh_get_error(session));
		ssh_channel_free(channel);
		ssh_disconnect(session);
		ssh_free(session);

	}

	rc = ssh_channel_request_pty(channel);
	if (rc != SSH_OK) return;

	rc = ssh_channel_change_pty_size(channel, 80, 24);
	if (rc != SSH_OK) return;

	rc = ssh_channel_request_shell(channel);
	if (rc != SSH_OK) return;

	int nbytes, nwritten;
	char buffer[256];
    int exit_status = -1;
	QEventLoop loopbig;
	QTimer::singleShot(5000, &loopbig, &QEventLoop::quit);

	while (true)
	{
		// 使用ssh_channel_read_nonblocking()非阻塞读取数据
		nbytes = ssh_channel_read_nonblocking(channel, buffer, sizeof(buffer), 0);
		if (nbytes > 0)
		{
			// 将buffer中的内容添加到QTextEdit中
			//ui->textEdit->append(QString::fromLatin1(buffer, nbytes));
		}
		else if (nbytes == SSH_ERROR || nbytes == 0)
		{
			// 处理错误
			break;
		}
		// 等待一段时间，避免CPU占用过高
		QEventLoop loop;
		QTimer::singleShot(100, &loop, &QEventLoop::quit);
		loop.exec();
	}
	loopbig.exec();

	QString command1 = "sys\n";
	ssh_channel_write(channel, command1.toStdString().c_str(), command1.size());
	QThread::sleep(5);
	while (true)
	{
		// 等待一段时间，避免CPU占用过高
		
		// 使用ssh_channel_read_nonblocking()非阻塞读取数据
		nbytes = ssh_channel_read_nonblocking(channel, buffer, sizeof(buffer), 0);
		if (nbytes > 0)
		{
			// 将buffer中的内容添加到QTextEdit中
			//ui->textEdit->append(QString::fromLatin1(buffer, nbytes));
		}
		else if (nbytes == SSH_ERROR || nbytes == 0)
		{
			// 处理错误
			break;
		}
	/*	QEventLoop loop;
		QTimer::singleShot(100000, &loop, &QEventLoop::quit);
		loop.exec();*/
		
	}
	//loopbig.exec();

	QString command2 = "in vl 10\n";
	ssh_channel_write(channel, command2.toStdString().c_str(), command2.size());
	QThread::sleep(5);
	while (true)
	{
		// 使用ssh_channel_read_nonblocking()非阻塞读取数据
		nbytes = ssh_channel_read_nonblocking(channel, buffer, sizeof(buffer), 0);
		if (nbytes > 0)
		{
			// 将buffer中的内容添加到QTextEdit中
			//ui->textEdit->append(QString::fromLatin1(buffer, nbytes));
		}
		else if (nbytes == SSH_ERROR || nbytes == 0)
		{
			// 处理错误
			break;
		}
		// 等待一段时间，避免CPU占用过高
		QEventLoop loop;
		QTimer::singleShot(100, &loop, &QEventLoop::quit);
		loop.exec();
	}

	QString command3 = "dis th\n";
	ssh_channel_write(channel, command3.toStdString().c_str(), command3.size());
	QThread::sleep(5);
	while (true)
	{
		// 使用ssh_channel_read_nonblocking()非阻塞读取数据
		nbytes = ssh_channel_read_nonblocking(channel, buffer, sizeof(buffer), 0);
		if (nbytes > 0)
		{
			// 将buffer中的内容添加到QTextEdit中
			ui->textEdit->append(QString::fromLatin1(buffer, nbytes));
		}
		else if (nbytes == SSH_ERROR || nbytes == 0)
		{
			// 处理错误
			break;
		}
		// 等待一段时间，避免CPU占用过高
		QEventLoop loop;
		QTimer::singleShot(100, &loop, &QEventLoop::quit);
		loop.exec();
	}

	ssh_channel_send_eof(channel);
	ssh_channel_close(channel);
	ssh_channel_free(channel);
	ssh_disconnect(session);
	ssh_free(session);
}



DataSqlshow::~DataSqlshow()
{
    dbshow.close();
    delete ui;

}

void DataSqlshow::on_pushButton_clicked()
{
    //EditData *editdata = new EditData;
    //editdata->show();
    MainWindow* mainwindow = new MainWindow(this);
    mainwindow->show();
    mainwindow->showbutton(0,1);

    connect(mainwindow, SIGNAL(freshData()), this, SLOT(on_pushButton_5_clicked()));

}









